# src/report_generator.py
import openpyxl
import xlrd
from xlutils.copy import copy as xl_copy
import logging
from typing import List, Dict


def _handle_xlsx_template(workbook, headers, data):
    """处理 .xlsx 格式的模板."""
    sheet = workbook.active
    header_row_index, template_header_order = _find_header_row(sheet, headers)

    if header_row_index == -1:
        raise ValueError(f"无法在模板中找到匹配的表头。期望表头: {headers}")

    start_row = header_row_index + 1
    for row_idx, data_row in enumerate(data, start=start_row):
        for col_idx, header_name in enumerate(template_header_order, start=1):
            sheet.cell(row=row_idx, column=col_idx, value=data_row.get(header_name))

    return workbook


def _handle_xls_template(workbook_rd, headers, data):
    """处理 .xls 格式的模板."""
    sheet_rd = workbook_rd.sheet_by_index(0)

    # 将 xlrd 对象转换为 xlwt 对象以便写入
    workbook_wt = xl_copy(workbook_rd)
    sheet_wt = workbook_wt.get_sheet(0)

    header_row_index, template_header_order = _find_header_row(sheet_rd, headers)

    if header_row_index == -1:
        raise ValueError(f"无法在模板中找到匹配的表头。期望表头: {headers}")

    start_row = header_row_index + 1  # .xls 行号从0开始, 但我们找到的已经是索引
    for row_idx, data_row in enumerate(data, start=start_row):
        for col_idx, header_name in enumerate(template_header_order):
            sheet_wt.write(row_idx, col_idx, data_row.get(header_name))

    return workbook_wt


def _find_header_row(sheet, db_headers):
    """
    在工作表中查找表头所在的行。
    兼容 openpyxl 和 xlrd 的 sheet 对象。
    """
    cleaned_db_headers = {str(h).strip() for h in db_headers}
    max_rows_to_scan = min(sheet.nrows, 5) if hasattr(sheet, 'nrows') else min(sheet.max_row, 5)

    for i in range(max_rows_to_scan):
        row_index = i + 1 if hasattr(sheet, 'max_row') else i  # openpyxl is 1-based, xlrd is 0-based

        # 获取行数据，兼容两种库
        if hasattr(sheet, 'iter_rows'):  # openpyxl
            # sheet[row_index] returns a tuple of cells
            current_row_values = [cell.value for cell in sheet[row_index]]
        else:  # xlrd
            current_row_values = sheet.row_values(row_index)

        cleaned_excel_headers = {str(v).strip() for v in current_row_values if v is not None and v != ''}

        if cleaned_db_headers == cleaned_excel_headers:
            logging.info(f"Header row found at index {row_index} in template.")
            final_header_order = [str(v).strip() for v in current_row_values if v is not None and v != '']
            return (row_index if hasattr(sheet, 'max_row') else i), final_header_order

    return -1, []


def create_report_file_from_template(
        base_template_path: str,
        output_path: str,
        headers: List[str],
        data: List[Dict]
):
    """
    打开一个基础模板Excel(.xlsx 或 .xls)，找到表头行，然后在其下方填充数据。
    """
    try:
        if base_template_path.lower().endswith('.xlsx'):
            # 使用 openpyxl 处理 .xlsx 文件
            workbook = openpyxl.load_workbook(base_template_path)
            workbook = _handle_xlsx_template(workbook, headers, data)
            workbook.save(output_path)

        elif base_template_path.lower().endswith('.xls'):
            # 使用 xlrd/xlwt/xlutils 处理 .xls 文件
            workbook_rd = xlrd.open_workbook(base_template_path, formatting_info=True)
            workbook_wt = _handle_xls_template(workbook_rd, headers, data)
            workbook_wt.save(output_path)

        else:
            raise ValueError("不支持的文件格式，请提供 .xlsx 或 .xls 文件。")

        logging.info(f"Successfully generated report at: {output_path}")

    except FileNotFoundError:
        logging.error(f"Template file not found at: {base_template_path}")
        raise
    except Exception as e:
        logging.error(f"An error occurred while generating the report: {e}")
        raise